import MySQLdb, string, tagpot, datetime
from rel import relation

cursor = tagpot.dbConnect()

def literal(val):
    if val:
        return MySQLdb.string_literal(val)
    else:
        return "NULL"

def isContain(tb, word):
    sql = "select id from %s where word = %s" %(tb, word)
    cursor.execute(sql)
    return cursor.fetchall()

def addRec(tb, word, anchor):
    sql = "insert into %s (word,anchor) values (%s, %s)" %(tb,  word, anchor)
    cursor.execute(sql)

def readRec(tb, target, a,b):
    sql = "select %s from %s where %s = %s" %(target, tb, a, b)
    cursor.execute(sql)
    return cursor.fetchall()

def updateRec(tb, target, value, id):
    sql = "update %s set %s = %s where id=%s" %(tb, target, value, id)
    cursor.execute(sql)

def indexRel(word, anc):
    anc.strip()
    q = "select ko, en from anc where anchor ='%s'" % (anc)
    cursor.execute(q)
    REL = cursor.fetchall()
    for r in REL:
        for i in r:
            rw = i.split(",")
            for k in rw:
                if k is '':
                    continue
                q = "select num from rel where a = '%s' and b = '%s'" % (word.lower(), k.lower())
                cursor.execute(q)
                ok = cursor.fetchall()
                if (ok == ()):
                    num = 1
                    q = "insert into rel(a,b,num) values ('%s','%s',%s)" % (word.lower(), k.lower(),num)
                    cursor.execute(q)
                else:
                    num = ok[0][0] + 1
                    q = "update rel SET num = %s WHERE a = '%s' and b = '%s'" % (num, word.lower(), k.lower())
                    cursor.execute(q)


today = datetime.date.today()
diff = datetime.timedelta(days=-1)
yesterday = today + diff 
cursor.execute("""select * from anc where date = %s""", yesterday)
#cursor.execute("""select * from anc """)
ok = cursor.fetchall()



for r in ok:
     # r[0]: id     r[1]: anchor    r[2]: ko    r[3]: en    r[4]: link    r[5]: count
     # parse words
     # for Korean 
     boom = string.split(r[2], ',')
     for wds in boom:
         wds = string.lstrip(wds)
         if wds is not '':
            indexRel(wds, r[1])
            
            wds = literal(wds)
            id = isContain('word', wds)
            if id is ():
                addRec('word', wds, literal(r[1]))
                
            else:
                c = readRec('word', 'id,anchor,hit','word', wds)
                hit = c[0][2] + 1
                star = string.split(c[0][1], ',')
                for acs in star:
                    if acs == r[1]:
                        updateRec('word', 'hit', literal(hit), c[0][0])
                        continue
                h = c[0][1]
                h = h+"," +r[1]
                updateRec('word', 'anchor', literal(h), c[0][0])
                updateRec('word', 'hit', literal(hit), c[0][0])
            
            
    
     # for English
     boom = string.split(r[3], ',')
     for wds in boom:
         wds = string.lstrip(wds)
         if wds is not '':
            indexRel(wds, r[1])
            wds = literal(wds)
            id = isContain('word', wds)
            if id is ():
                addRec('word', wds, literal(r[1]))
            else:
                c = readRec('word', 'id,anchor,hit','word', wds)
                hit = c[0][2] + 1
                star = string.split(c[0][1], ',')
                for acs in star:
                    if acs == r[1]:
                        updateRec('word', 'hit', literal(hit), c[0][0])
                        continue
                h = c[0][1]
                h = h+"," +r[1]
                updateRec('word', 'anchor', literal(h), c[0][0])
                updateRec('word', 'hit', literal(hit), c[0][0])
            
            
                
                
